package com.liliya.dao.impl;

import com.liliya.dao.BaseDao;
import com.liliya.dao.SubCateDao;
import com.liliya.pojo.Cate;
import com.liliya.pojo.SubCate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description TODO
 * @Author hhy
 * @Date 2021/1/10 16:54
 */
public class SubCateDaoImpl extends BaseDao implements SubCateDao {
    @Override
    public void insertSubCate(SubCate subCate) {
        String sql = "insert into T_SUBCATE (SUBCATENAME, CATENUM, SUBCATEDES, SUBCATIMGLINK, SUBCATESTYLE, SUBCATENUM)\n" +
                "                                                                values (?,?,?,?,?,subcateid.nextval)";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, subCate.getSubcatename());
            pstm.setInt(2, subCate.getCatenum());
            pstm.setString(3, subCate.getSubcatedes());
            pstm.setString(4, subCate.getSubcatimglink());
            pstm.setString(5, subCate.getSubcatestyle());
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
    }

    @Override
    public void deleteSubCate(Integer subCateNum) {
        String sql = "delete T_SUBCATE where SUBCATENUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, subCateNum);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
    }

    @Override
    public List<SubCate> findSubCate(Integer start, Integer end, Integer catenum) {
        List<SubCate> subCates = new ArrayList<>();
        String sql = "select subcatename, catenum, subcatedes, subcatimglink, subcatestyle, subcatenum\n" +
                "from (select subcatename, catenum, subcatedes, subcatimglink, subcatestyle, subcatenum,ROWNUM r from T_SUBCATE) where r between ? and ? and CATENUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, start);
            pstm.setInt(2, end);
            pstm.setInt(3, catenum);
            rs = pstm.executeQuery();
            while (rs.next()) {
                SubCate subCate = new SubCate();
                subCate.setSubcatename(rs.getString("subcatename"));
                subCate.setCatenum(rs.getInt("catenum"));
                subCate.setSubcatedes(rs.getString("subcatedes"));
                subCate.setSubcatimglink(rs.getString("subcatimglink"));
                subCate.setSubcatestyle(rs.getString("subcatestyle"));
                subCate.setSubcatenum(rs.getInt("subcatenum"));
                subCates.add(subCate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
        return subCates;
    }

    @Override
    public void updateSubCate(SubCate subCate) {
        String sql = "update T_SUBCATE set subcatename=?,\n" +
                "                     catenum=?,\n" +
                "                     subcatedes=?,\n" +
                "                     subcatimglink=?,\n" +
                "                     subcatestyle=?\n" +
                "where subcatenum = ?";
            try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,subCate.getSubcatename());
            pstm.setInt(2,subCate.getCatenum());
            pstm.setString(3,subCate.getSubcatedes());
            pstm.setString(4,subCate.getSubcatimglink());
            pstm.setString(5,subCate.getSubcatestyle());
            pstm.setInt(6,subCate.getSubcatenum());
            pstm.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        System.out.println(sql);
    }

    @Override
    public int totalSubCate() {
        int total = 0;
        String sql = "select count(1) c from T_SUBCATE";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                total = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
        return total;
    }

    @Override
    public SubCate findSubCateByNum(Integer subCateNum) {
        SubCate subCate = null;
        String sql = "select subcatename, catenum, subcatedes, subcatimglink, subcatestyle, subcatenum from T_SUBCATE where SUBCATENUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, subCateNum);
            rs = pstm.executeQuery();
            while (rs.next()) {
                subCate = new SubCate();
                subCate.setSubcatename(rs.getString("subcatename"));
                subCate.setCatenum(rs.getInt("catenum"));
                subCate.setSubcatedes(rs.getString("subcatedes"));
                subCate.setSubcatimglink(rs.getString("subcatimglink"));
                subCate.setSubcatestyle(rs.getString("subcatestyle"));
                subCate.setSubcatenum(rs.getInt("subcatenum"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println(sql);
        return subCate;
    }
}
